﻿using System;
using System.Collections.Generic;
using System.Globalization;
using SpreadsheetGear;
using Sustainalytics.DataLayer;
using Sustainalytics.Entities;
using Sustainalytics.Portfolios.BL.Model;
using Sustainalytics.Utils;

namespace Sustainalytics.Portfolios.BL
{
    public class PortfolioExportManagement
    {
        private readonly IPortfolioReadHandler _portfolioManager;
        private readonly IAccountsRepository _accountRepo;

        public PortfolioExportManagement(IPortfolioReadHandler portfolio, IAccountsRepository accountRepo)
        {
            _portfolioManager = portfolio;
            _accountRepo = accountRepo;
        }

        public byte[] GeneratePortfolioFile(Guid portfolioId, string fileName, PortfolioUserClaims userClaims)
        {
            var uploadPortfolioDetails = _portfolioManager.GetPortfolioSecurities(portfolioId, userClaims);
            var portfolio = _portfolioManager.GetPortfolioById(portfolioId, userClaims);

            // Create a workbook with some cell data
            var workbook = SpreadsheetGear.Factory.GetWorkbookSet();
            var portfolioExportWorkBook = workbook.Workbooks.Open(fileName);
            if (portfolioExportWorkBook == null)
            {
                throw new NullReferenceException("portfolioWorkbook");
            }
            var coverSheet = portfolioExportWorkBook.Worksheets[0];
            var resultSheet = portfolioExportWorkBook.Worksheets[1];

            // write cover sheet
            if (!portfolio.AccountIds.IsNullOrEmpty())
            {
                string accountName = _accountRepo.GetAccountById(userClaims.AccountId).Name;
                WriteCoverSheet(portfolio, coverSheet, accountName);
            }
            WriteResultSheet(uploadPortfolioDetails, resultSheet);
            return portfolioExportWorkBook.SaveToMemory(FileFormat.OpenXMLWorkbook);
        }

        private static void WriteCoverSheet(Portfolio portfolio, IWorksheet coverSheet, string accountName)
        {
            if (coverSheet == null)
            {
                throw new ArgumentNullException("coverSheet");
            }
            if (portfolio.DateModified.HasValue)
            {
                coverSheet.Cells["D3"].Value = (portfolio.DateModified.Value).ToString("dd/MM/yyyy",
                    CultureInfo.InvariantCulture);
            }
            coverSheet.Cells["D4"].Value = portfolio.Name;
            coverSheet.Cells["D5"].Value = accountName;
        }

        private static void WriteResultSheet(IEnumerable<PortfolioSecurityModel> uploadPortfolioDetails, IWorksheet resultSheet)
        {
            if (resultSheet == null)
            {
                throw new ArgumentNullException("resultSheet");
            }
            var cells = resultSheet.Cells;

            var row = 1;

            foreach (var item in uploadPortfolioDetails)
            {
                //Security name – the securiy name from PortfolioDetails
                var column = 0;
                cells[row, column].Value = item.DisplayInfo.CustomSecurityIdentifierName;

                //Security ID (required) – the securiy id from PortfolioDetails
                column++;
                cells[row, column].Value = item.DisplayInfo.SecurityIdentifier;

                //Weight – the weight value from PortfolioDetails
                column++;
                cells[row, column].Value = item.SecurityWeight;

                //Matched Company name - the company name from PortfolioDetails
                column++;
                cells[row, column].Value = item.MatchedInfo.CompanyName;
                cells[row, column].Style.WrapText = true;

                //Matched Company CIQID - the capital IQ ID from PortfolioDetails
                column++;
                cells[row, column].Value = item.MatchedInfo.CapitalIQId;
                cells[row, column].Style.WrapText = true;

                column++;
                cells[row, column].Value = item.AccessDetails;
                cells[row, column].Style.WrapText = true;
                row++;
            }
        }


        public string GetTemplateForPortfolioSecurities(string fileName, string filePath)
        {
            IWorkbookSet workbook = SpreadsheetGear.Factory.GetWorkbookSet();
            var portfolioExportWorkBook = workbook.Workbooks.Open(filePath);

            portfolioExportWorkBook.SaveAs(fileName, FileFormat.OpenXMLWorkbook);

            return fileName;
        }
    }
}
